package com.ustcinfo.study.hive.r2.xujianan;
/*
 *
 * @Author: xujn
 * */

import java.sql.*;

/**
 * Hive连接工具类
 */
public class HiveConnectionUtil {
    private static String sql_read_table = "select * from dns_xujianan limit 10";
    private static String sql_show_table = "show tables";
    private static String sql_create_table = "create table dns_xujianan(" +
            "ip String,domain_name String, access_time String, target_ip String,status String)" +
            "row format delimited fields terminated by '#'";
    //    加载本地（linux）数据到====>hive
    private static String sql_load_file = "load data local inpath '/root/dnsSample' into table dns_xujianan";
    //域名统计
    private static String sql_domain_count = "select domain_name,count(*) from dns_xujianan group by domain_name";

    //    连接hive必备四条 url,name,psw,driver
    private static String url = "jdbc:hive2://node2:10000/default";

    private static String userName = "root";    //踩坑  MapReduce需要root用户

    private static String password = "";

    private static String driver = "org.apache.hive.jdbc.HiveDriver";

    private static Connection connection = null;

    private static Statement st = null;

    private static ResultSet rs = null;

    /*
     * 获取连接
     * */
    public static Connection getConnection() {
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url, userName, password);
        } catch (ClassNotFoundException | SQLException e) {
            System.out.println("hive connection failed");
        }
        return connection;
    }

    /**
     * 获取游标
     */
    public static Statement getSt(Connection conn, String sql) {
        Statement st = null;
        try {
            st = conn.createStatement();
            st.execute(sql);
        } catch (SQLException e) {
            System.out.println("get statement fail");
            ;
            e.printStackTrace();
        }
        return st;
    }

    /*
     * 获取结果集
     * */
    public static ResultSet getRs(Connection conn, String sql) {
        Statement st;
        ResultSet rs = null;
        try {
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            System.out.println(">>>>" + st + rs);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    /*
     * 创建hive表
     * */
    public static void createTable(Connection conn, String sql) {
        getSt(conn, sql);
    }

    /*
     * 域名统计
     * @param conn,sql
     * @return
     * */
    public static void domainCount(Connection conn, String sql) throws SQLException {
        rs = getRs(conn, sql);
        System.out.println(rs);
        while (rs.next()) {
            System.out.println("域名：" + rs.getString(1) + "******" + "个数:" + rs.getInt(2));
        }
    }


    public static void main(String[] args) throws SQLException {
//        创建表
        createTable(getConnection(), sql_create_table);
//        加载数据===>hive
        getSt(getConnection(), sql_load_file);
        domainCount(getConnection(), sql_domain_count);
//    查询十条数据
        ResultSet rs = getRs(getConnection(), sql_read_table);
        while (rs.next()) {
            System.out.println(rs.getString(1));
        }
//        域名统计
        domainCount(getConnection(), sql_domain_count);


    }

}
